import pymysql
from pymysql import OperationalError,ProgrammingError,InternalError
import traceback,re
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': '123456',
    'db': 'py00db',
    'charset': 'utf8'
}


def connect_db():
    try:
        connect = pymysql.connect(**config)
        return connect
    except OperationalError as e:
        # OperationalError 用于捕捉PyMySQL的错误机制，目的是不会出现异常
        print(e)


'''插入一条数据'''
def query_insert_one(table_name,data):
    # inser_data = [
    #     ('u_id', '12345678901'),
    #     ('u_pwd', '1123'),
    #     ('u_rol', 0)
    # ]
    inser_data = [
        ('u_id', '12345678901'),
        ('u_name', '王大锤'),
        ('u_tell', '12345678900'),
        ('u_adress', '牛家村')
    ]

    head = []
    values = []
    temps = []
    for item in inser_data:
        head.append(item[0])
        values.append(item[1])
        temps.append('%s')
    '''将 List 转换成 tuple 元组数据集合'''
    head = tuple(head)
    values = tuple(values)
    temps = tuple(temps)
    '''拼接 数据库执行语句 字符串'''
    base_url = "insert into "
    base_url += table_name + str(head) + ' values ' +str(temps)
    print(base_url)  # insert into stu('u_uid', 'u_name', 'u_age') values ('%s', '%s', '%s')
    base_url = re.sub(r"\'", '', base_url) # 用正则表达式去除所有de'
    print(base_url)  # 拼成如下效果 insert into stu(u_uid, u_name, u_age) values (%s, %s, %s)
    connect = connect_db()
    if connect:
        cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            '''使用 execute() 方法执行SQL语句 防注入'''
            cursor.execute(base_url,values)
            '''提交修改数据 并保存'''
            connect.commit()
        except InternalError as r:
            print(r)
        except:
            traceback.print_exc()
def main():
    # inser_data = [
    #     ('u_id', '12345678901'),
    #     ('u_pwd', '1123'),
    #     ('u_rol', 0)
    # ]
    # query_insert_one('users','',inser_data)



    query_insert_one('userinfo','')
if __name__ == '__main__':
    main()